# _*_ coding:utf-8 _*_
# @Time  : 2023.07.19
# @Author: zizlee
from fastapi import APIRouter, Body, Query
from pydantic import BaseModel, validator
from response import APIResponse
from utils import datetime_utils
from constant.user import UserType
from db_utils import BaseDatabaseConnection


class FlexBlock(BaseModel):
    name: str


class ModifyFlexBlock(FlexBlock):
    bid: int
    name: str
    is_active: bool


class FlexBlockUser(BaseModel):
    flex_block_id: int
    user_id: int
    active_expire: str

    @validator('active_expire')
    def validate_active_expire(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('active_expire invalidate! must be `yyyy-MM-dd`')
        return value


class CreateFlexBlockUser(FlexBlockUser):
    pass


class FlexBlockVarietyUser(BaseModel):
    flex_block_id: int
    variety_en: str
    user_id: int
    active_expire: str

    @validator('active_expire')
    def validate_active_expire(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('active_expire invalidate! must be `yyyy-MM-dd`')
        return value


class CreateFlexBlockVarietyUser(FlexBlockVarietyUser):
    pass


# 用户定制板块品种下的指标
class FlexUserIndexItem(BaseModel):
    user_id: int
    index_id: int
    block_id: int
    variety_en: str
    index_name: str
    active_expire: str
    lasted_years: int = None
    start_date: str = None
    end_date: str = None
    note: str = ''

    @validator('active_expire')
    def validate_active_expire(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('active_expire invalidate! must be `yyyy-MM-dd`')
        return value

    @validator('start_date')
    def validate_start_date(cls, value):
        return datetime_utils.auth_yyyy_mm_dd(value)

    @validator('end_date')
    def validate_end_date(cls, value):
        return datetime_utils.auth_yyyy_mm_dd(value)


class CreateFlexUserIndexItem(FlexUserIndexItem):
    pass


# 板块下新建数据条目组
class FlexBlockIndexGroup(BaseModel):
    block_id: int
    index_name: str
    frequency: str
    unit: str
    start_date: str = None
    end_date: str = None
    sort_num: int = 1
    note: str = ''


class CreateFlexBlockIndexGroup(FlexBlockIndexGroup):
    pass


class FlexBlockGroupIndexItem(BaseModel):
    index_id: int
    index_name: str
    block_group_id: int
    variety_en: str
    note: str


class CreateFlexBlockGroupIndexItem(FlexBlockGroupIndexItem):
    pass


class FlexBlockIndexGroupUser(BaseModel):
    block_group_id: int
    user_id: int
    active_expire: str
    lasted_years: int = None
    start_date: str = None
    end_date: str = None
    note: str = ''

    @validator('active_expire')
    def validate_active_expire(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('active_expire invalidate! must be `yyyy-MM-dd`')
        return value

    @validator('start_date')
    def validate_start_date(cls, value):
        return datetime_utils.auth_yyyy_mm_dd(value)

    @validator('end_date')
    def validate_end_date(cls, value):
        return datetime_utils.auth_yyyy_mm_dd(value)


class CreateFlexBlockIndexGroupUser(FlexBlockIndexGroupUser):
    pass


class FlexDatalibBlock:

    def get_user_obj(self, conn: BaseDatabaseConnection, user_id: int):
        # 查询用户类型，有些用户无需设置
        conn.cursor.execute("SELECT id,user_type FROM ruizhi_user WHERE id=%s LIMIT 1;", user_id)
        user = conn.cursor.fetchone()
        return user

    def create_block(self, block_name: str):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("INSERT INTO datlib_flex_block (block_name) VALUES (%s);", [block_name])
        return APIResponse.success(msg='创建新的定制板块名称成功!')

    def retrieve_block(self):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("SELECT * FROM datlib_flex_block ORDER BY sort_num;")
            block_list = conn.cursor.fetchall()
        return APIResponse.success(data={'blocks': block_list})

    def modify_block(self, block_item: ModifyFlexBlock):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("UPDATE datlib_flex_block SET `name`=%s,is_active=%s WHERE id=%s LIMIT 1;",
                                [block_item.name, block_item.is_active, block_item.bid])
        return APIResponse.success()

    def create_block_user(self, block_user: CreateFlexBlockUser):
        with BaseDatabaseConnection() as conn:
            # 查询用户类型，有些用户无需设置
            user = self.get_user_obj(conn, user_id=block_user.user_id)
            if not user or user['user_type'] not in UserType.SECTION_AUTH_SETS:
                return APIResponse.validate_error(detail='用户不存在或该用户无需配置定制板块权限!')
            conn.cursor.execute("SELECT id FROM datlib_flex_block_user WHERE flex_block_id=%s AND user_id=%s LIMIT 1;",
                                [block_user.flex_block_id, block_user.user_id])
            block = conn.cursor.fetchone()
            if block:
                sql = "UPDATE datlib_flex_block_user SET active_expire=%s WHERE id=%s LIMIT 1;"
                prm = [block_user.active_expire, block['id']]
            else:
                sql = "INSERT INTO datlib_flex_block_user (flex_block_id,user_id,active_expire) VALUES (%s,%s,%s);"
                prm = [block_user.flex_block_id, block_user.user_id, block_user.active_expire]
            c = conn.cursor.execute(sql, prm)
        return APIResponse.success(msg=f'操作成功，{c}个数据发生改变!')

    def retrieve_block_user(self, user_id: int):
        with BaseDatabaseConnection() as conn:
            user = self.get_user_obj(conn, user_id=user_id)
            if not user:
                return APIResponse.success(data={'blocks': []})
            if user['user_type'] not in UserType.SECTION_AUTH_SETS:  # 返回全部
                sql = "SELECT id,block_name,sort_num FROM datlib_flex_block WHERE is_active=1 ORDER BY sort_num;"
                prm = None
            else:
                sql = """
                    SELECT a.*,b.block_name,b.sort_num FROM datlib_flex_block_user As a 
                    INNER JOIN datlib_flex_block As b
                    ON a.flex_block_id=b.id
                    WHERE a.user_id=%s
                    ORDER BY b.sort_num;
                """
                prm = [user_id]
            conn.cursor.execute(sql, prm)
            user_blocks = conn.cursor.fetchall()
        return APIResponse.success(data={'blocks': user_blocks})

    def create_block_variety_user(self, block_variety_user: CreateFlexBlockVarietyUser):
        with BaseDatabaseConnection() as conn:
            user = self.get_user_obj(conn, user_id=block_variety_user.user_id)
            if not user or user['user_type'] not in UserType.VARIETY_AUTH_SETS:
                return APIResponse.validate_error(detail='用户不存在，或用户默认全权限或无权限，无需设置!')
            conn.cursor.execute("""
                SELECT id FROM datlib_flex_block_variety_user
                WHERE flex_block_id=%s AND user_id=%s AND variety_en=%s LIMIT 1;
            """, [block_variety_user.flex_block_id, block_variety_user.user_id, block_variety_user.variety_en])
            variety = conn.cursor.fetchone()
            if variety:
                sql = "UPDATE datlib_flex_block_variety_user SET active_expire=%s WHERE id=%s LIMIT 1;"
                prm = [block_variety_user.active_expire, variety['id']]
            else:
                sql = """
                    INSERT INTO datlib_flex_block_variety_user (flex_block_id,variety_en,user_id,active_expire)
                    VALUES (%s,%s,%s,%s);
                """
                prm = [block_variety_user.flex_block_id, block_variety_user.variety_en,
                       block_variety_user.user_id, block_variety_user.active_expire]
            c = conn.cursor.execute(sql, prm)
        return APIResponse.success(msg=f'操作板块下用户品种成功，{c}个数据发生改变!')

    def retrieve_block_variety_user(self, user_id: int, block_id: int):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
                SELECT x.id,x.flex_block_id,x.block_name,x.sort_num,x.variety_en,x.active_expire,y.name FROM 
                    (SELECT a.id,a.flex_block_id,a.variety_en,a.user_id,a.active_expire,
                    b.block_name,b.sort_num 
                    FROM datlib_flex_block_variety_user As a 
                    INNER JOIN datlib_flex_block As b
                    ON a.flex_block_id=b.id
                    WHERE a.user_id=%s AND a.flex_block_id=%s
                    ORDER BY b.sort_num) As x
                INNER JOIN ruizhi_variety As y ON x.variety_en=y.code
                ORDER BY y.code;
            """, [user_id, block_id])
            block_variety = conn.cursor.fetchall()
        return APIResponse.success(data={'block_variety': block_variety})


class FlexDatalibIndex:
    def get_user_obj(self, conn: BaseDatabaseConnection, user_id: int):
        # 查询用户类型，有些用户无需设置
        conn.cursor.execute("SELECT id,user_type FROM ruizhi_user WHERE id=%s LIMIT 1;", user_id)
        user = conn.cursor.fetchone()
        return user

    def create_user_flex_index(self, flex_item: CreateFlexUserIndexItem):
        exist_sql = """
            SELECT id FROM datlib_flex_index_user WHERE user_id=%s AND block_id=%s AND variety_en=%s LIMIT 1;
        """
        exist_prm = [flex_item.user_id, flex_item.block_id, flex_item.variety_en]
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(exist_sql, exist_prm)
            exist_index = conn.cursor.fetchone()
            if exist_index:
                sql = """
                    UPDATE datlib_flex_index_user SET index_name=%s,lasted_years=%s,start_date=%s,end_date=%s,active_expire=%s
                    WHERE id=%s LIMIT 1;
                """
                prm = [flex_item.index_name, flex_item.lasted_years, flex_item.start_date, flex_item.end_date, flex_item.active_expire,
                       exist_index['id']]
            else:
                sql = """
                    INSERT INTO datlib_flex_index_user(user_id,index_id,block_id,variety_en,index_name,lasted_years,
                    start_date,end_date,active_expire,note) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
                """
                prm = [flex_item.user_id, flex_item.index_id, flex_item.block_id, flex_item.variety_en, flex_item.index_name, flex_item.lasted_years,
                       flex_item.start_date, flex_item.end_date, flex_item.active_expire, flex_item.note]
            c = conn.cursor.execute(sql, prm)
        return APIResponse.success(msg=f'操作完成，影响{c}个数据!')

    def retrieve_user_flex_index(self, user_id: int, block_id: int, variety_en: str):
        sql = """
            SELECT u.*,i.name,i.plat_id,i.db_name,i.tb_name,i.frequency,i.unit,i.start_date as i_start,i.end_date as i_end
            FROM datlib_flex_index_user As u
            INNER JOIN datlib_index As i ON u.index_id=i.id
            WHERE u.user_id=%s AND u.block_id=%s AND u.variety_en=%s
            ORDER BY u.sort_num;
        """
        prm = [user_id, block_id, variety_en]
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, prm)
            flex_indexes = conn.cursor.fetchall()
        return APIResponse.success(data={'indexes': flex_indexes})

    def create_block_index_group(self, group_item: CreateFlexBlockIndexGroup):
        with BaseDatabaseConnection() as conn:
            c = conn.cursor.execute("""
                INSERT INTO datlib_flex_block_group (block_id,index_name,frequency,unit,start_date,end_date,sort_num)
                VALUES (%s,%s,%s,%s,%s,%s,%s);
            """, [group_item.block_id, group_item.index_name, group_item.frequency, group_item.unit,
                  group_item.start_date, group_item.end_date, group_item.sort_num])

        return APIResponse.success(msg=f'操作成功，影响数据{c}条!')

    def retrieve_block_index_group(self, block_id: int):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
                SELECT a.id,a.block_id,a.frequency,a.index_name,a.start_date,a.end_date,a.sort_num,b.block_name
                FROM datlib_flex_block_group As a 
                INNER JOIN datlib_flex_block As b ON a.block_id=b.id
                WHERE a.block_id=%s
                ORDER BY a.sort_num;
            """, [block_id])
            group_list = conn.cursor.fetchall()

        return APIResponse.success(data={'groups': group_list})

    def create_block_index_group_index(self, index_item: CreateFlexBlockGroupIndexItem):
        exist_sql = """
            SELECT id FROM datlib_flex_block_group_index WHERE index_id=%s AND block_group_id=%s AND variety_en=%s LIMIT 1;
        """
        exist_prm = [index_item.index_id, index_item.block_group_id, index_item.variety_en]
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(exist_sql, exist_prm)
            exist_index = conn.cursor.fetchone()
            if exist_index:
                sql = """
                    UPDATE datlib_flex_block_group_index SET index_name=%s,note=%s WHERE id=%s LIMIT 1;
                """
                prm = [index_item.index_name, index_item.note, exist_index['id']]
            else:
                sql = """
                    INSERT INTO datlib_flex_block_group_index(index_id,index_name,block_group_id,variety_en,note)
                    VALUES (%s,%s,%s,%s,%s);
                """
                prm = [index_item.index_id, index_item.index_name, index_item.block_group_id, index_item.variety_en,
                       index_item.note]
            c = conn.cursor.execute(sql, prm)
        return APIResponse.success(msg=f'操作完成，影响{c}个数据!')

    def retrieve_block_index_group_index(self, block_group_id: int, variety_en: str = None):
        variety = '0' if not variety_en else variety_en
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
                SELECT a.id,a.index_id,a.index_name,a.block_group_id,a.variety_en,a.note,a.is_active,
                b.frequency,b.plat_id,b.db_name,b.tb_name,b.unit,b.start_date,b.end_date
                FROM datlib_flex_block_group_index As a
                INNER JOIN datlib_index As b ON a.index_id=b.id
                WHERE a.block_group_id=%s AND IF ('0'=%s,TRUE,a.variety_en=%s)
                ORDER BY a.sort_num;
            """, [block_group_id, variety, variety_en])
            index_list = conn.cursor.fetchall()
        return APIResponse.success(data={'index_list': index_list})

    def create_block_index_group_user(self, create_item: CreateFlexBlockIndexGroupUser):
        exist_sql = """
            SELECT id FROM datlib_flex_block_group_user WHERE user_id=%s AND block_group_id=%s LIMIT 1;
        """
        exist_prm = [create_item.user_id, create_item.block_group_id]
        with BaseDatabaseConnection() as conn:
            user = self.get_user_obj(conn, user_id=create_item.user_id)
            if not user or user['user_type'] not in UserType.VARIETY_AUTH_SETS:
                return APIResponse.validate_error(detail='用户不存在或该用户无需设定数据组，默认全有或全无!')
            conn.cursor.execute(exist_sql, exist_prm)
            exist_index = conn.cursor.fetchone()
            if exist_index:
                sql = """
                    UPDATE datlib_flex_block_group_user SET lasted_years=%s,start_date=%s,end_date=%s,active_expire=%s,
                    note=%s
                    WHERE id=%s LIMIT 1;
                """
                prm = [create_item.lasted_years, create_item.start_date, create_item.end_date,
                       create_item.active_expire, create_item.note, exist_index['id']]
            else:
                sql = """
                    INSERT INTO datlib_flex_block_group_user(block_group_id,user_id,start_date,end_date,active_expire,note)
                    VALUES (%s,%s,%s,%s,%s,%s);
                """
                prm = [create_item.block_group_id, create_item.user_id, create_item.start_date, create_item.end_date,
                       create_item.active_expire, create_item.note]
            c = conn.cursor.execute(sql, prm)
        return APIResponse.success(msg=f'操作完成，影响{c}个数据!')

    def retrieve_block_index_group_user(self, user_id, block_id):
        with BaseDatabaseConnection() as conn:
            user = self.get_user_obj(conn, user_id=user_id)
            if user and user['user_type'] not in UserType.VARIETY_AUTH_SETS:
                sql = """
                    SELECT id,block_id,index_name,frequency,unit,sort_num FROM datlib_flex_block_group WHERE block_id=%s ORDER BY sort_num;
                """
                prm = [block_id]
            else:
                sql = """
                    SELECT a.id,a.block_group_id,a.lasted_years,a.start_date,a.end_date,a.active_expire,a.note,
                    b.index_name,frequency,unit,sort_num
                    FROM datlib_flex_block_group_user As a
                    INNER JOIN datlib_flex_block_group As b ON a.block_group_id=b.id
                    WHERE a.user_id=%s AND b.block_id=%s ORDER BY b.sort_num;
                """
                prm = [user_id, block_id]
            conn.cursor.execute(sql, prm)
            group_list = conn.cursor.fetchall()
        return APIResponse.success(data={'index_list': group_list})


flex_datalib_api = APIRouter()


@flex_datalib_api.post('/block/create/')  # 创建板块
def create_flex_datalib_block(block_name: str = Body(..., embed=True)):
    return FlexDatalibBlock().create_block(block_name=block_name)


@flex_datalib_api.get('/block/retrieve/')  # 获取板块列表
def retrieve_flex_datalib_block():
    return FlexDatalibBlock().retrieve_block()


@flex_datalib_api.put('/block/modify/')  # 修改板块
def modify_flex_datalib_block(item: ModifyFlexBlock = Body(...)):
    return FlexDatalibBlock().modify_block(block_item=item)


@flex_datalib_api.post('/blockUser/create/')  # 定制板块关联到用户
def create_block_user(item: CreateFlexBlockUser = Body(...)):
    return FlexDatalibBlock().create_block_user(block_user=item)


@flex_datalib_api.get('/blockUser/retrieve/')  # 用户定制的板块
def retrieve_block_user(uid: int = Query(...)):
    return FlexDatalibBlock().retrieve_block_user(user_id=uid)


@flex_datalib_api.post('/blockVarietyUser/create/')  # 创建用户板块下的品种
def create_block_variety_user(item: CreateFlexBlockVarietyUser = Body(...)):
    return FlexDatalibBlock().create_block_variety_user(block_variety_user=item)


@flex_datalib_api.get('/blockVarietyUser/retrieve/')  # 获取用户板块下的品种
def retrieve_block_variety_user(uid: int = Query(...), bid: int = Query(...)):
    return FlexDatalibBlock().retrieve_block_variety_user(user_id=uid, block_id=bid)


@flex_datalib_api.post('/blockIndexGroup/create/')  # 创建板块下的数据组
def create_block_group_name(group: CreateFlexBlockIndexGroup = Body(...)):
    return FlexDatalibIndex().create_block_index_group(group_item=group)


@flex_datalib_api.get('/blockIndexGroup/retrieve/')  # 查询板块下的数据组
def retrieve_block_group_name(bid: int = Query(...)):
    return FlexDatalibIndex().retrieve_block_index_group(block_id=bid)


@flex_datalib_api.get('/blockIndexGroup/create/')  # 创建板块下的数据组
def retrieve_block_group_name(bid: int = Query(...)):
    return FlexDatalibIndex().retrieve_block_index_group(block_id=bid)


@flex_datalib_api.get('/blockGroupIndex/retrieve/')  # 查询组内的指标
def create_block_group_index(bgid: int = Query(...), ven: str = Query(None)):
    return FlexDatalibIndex().retrieve_block_index_group_index(block_group_id=bgid, variety_en=ven)


@flex_datalib_api.post('/blockGroupIndex/create/')  # 创建组内的指标
def create_block_group_index(item: CreateFlexBlockGroupIndexItem = Body(...)):
    return FlexDatalibIndex().create_block_index_group_index(index_item=item)


@flex_datalib_api.post('/blockIndexGroupUser/create/')  # 创建板块下数据包和用户的关系
def create_index_group_user(item: CreateFlexBlockIndexGroupUser = Body(...)):
    return FlexDatalibIndex().create_block_index_group_user(create_item=item)


@flex_datalib_api.get('/blockIndexGroupUser/retrieve/')  # 查询板块下数据包和用户的关系
def retrieve_index_group_user(uid: int = Query(...), bid: int = Query(...)):
    return FlexDatalibIndex().retrieve_block_index_group_user(user_id=uid, block_id=bid)


# 接口2个接口功能无用，暂时弃用。用户板块下的品种内的自由定制的指标。
# @flex_datalib_api.post('/indexUser/create/')  # 创建用户板块品种下的指标
# def create_flex_index_user(flex_item: CreateFlexUserIndexItem = Body(...)):
#     return FlexDatalibIndex().create_user_flex_index(flex_item=flex_item)
#
#
# @flex_datalib_api.get('/indexUser/retrieve/')  # 查询用户板块品种下的指标
# def create_flex_index_user(uid: int = Query(...), bid: int = Query(...), ven: str = Query(...)):
#     return FlexDatalibIndex().retrieve_user_flex_index(user_id=uid, block_id=bid, variety_en=ven)
#
